﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Rotempco.ORM.Generator.Objects;
using Rotempco.ORM.Generator.Enums;

namespace Rotempco.ORM.Generator.Logic
{
    public class DatabaseInfoRetrieverLogic
    {
        public static List<string> GetDatabases(string serverIp, bool useIntegrated = true, string username = null, string password = null)
        {
            return PerformSQLActions<List<string>>(serverIp, useIntegrated, username, password, null, (SqlConnection sqlConnection) =>
            {
                DataTable dataTable = sqlConnection.GetSchema(SqlClientMetaDataCollectionNames.Databases);

                return (from row in dataTable.Rows.Cast<DataRow>() select row[0] + "").ToList();
            });
        }

        public static List<string> GetTables(string serverIp, string databaseName, bool useIntegrated = true, string username = null, string password = null)
        {
            return PerformSQLActions<List<string>>(serverIp, useIntegrated, username, password, databaseName, (SqlConnection sqlConnection) =>
            {
                DataTable dataTable = sqlConnection.GetSchema(SqlClientMetaDataCollectionNames.Tables);

                return (from row in dataTable.Rows.Cast<DataRow>() where row[2].ToString() != "sysdiagrams" select row[2] + "").ToList();
            });
        }

        public static List<DatabaseColumn> GetColumns(string serverIp, string databaseName, string tableName, bool useIntegrated = true, string username = null, string password = null)
        {
            return PerformSQLActions<List<DatabaseColumn>>(serverIp, useIntegrated, username, password, databaseName, (SqlConnection sqlConnection) =>
            {
                string[] restrictions = new string[4] { databaseName, null, tableName, null };

                DataTable dataTable = sqlConnection.GetSchema(SqlClientMetaDataCollectionNames.Columns, restrictions);

                return (from row in dataTable.Rows.Cast<DataRow>()
                        orderby (int)row[4]
                        select new DatabaseColumn()
                            {
                                PrimaryKey = ((int)row[4] == 1),
                                Name = row[3].ToString(),
                                DeletedFlag = false,
                                EncryptionType = EncryptionTypeEnum.ClearText,
                                ColumnType = GetColumnType(row[7].ToString()),
                                Nullable = row[6].ToString().Equals("yes", StringComparison.InvariantCultureIgnoreCase)
                            }).ToList();

            });
        }


        public static List<DatabaseColumn> GetColumnsFromQuery(string serverIp, string databaseName, string sqlQuery, SqlParameter[] sqlParameters, bool useIntegrated = true, string username = null, string password = null)
        {
            return PerformSQLActions<List<DatabaseColumn>>(serverIp, useIntegrated, username, password, databaseName, (SqlConnection sqlConnection) =>
            {
                List<DatabaseColumn> result = new List<DatabaseColumn>();

                try
                {
                    if (sqlConnection.State != ConnectionState.Open)
                        sqlConnection.Open();

                    SqlCommand sqlCommand = sqlConnection.CreateCommand();
                    sqlCommand.CommandText = sqlQuery;

                    if (sqlParameters != null)
                        sqlCommand.Parameters.AddRange(sqlParameters);

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                    DataTable dataTable = new DataTable();
                    sqlDataAdapter.Fill(dataTable);


                    foreach (DataColumn myDataColumn in dataTable.Columns)
                    {
                        result.Add(new DatabaseColumn()
                        {
                            PrimaryKey = false,
                            Name = myDataColumn.ColumnName,
                            DeletedFlag = false,
                            EncryptionType = EncryptionTypeEnum.ClearText,
                            ColumnType = myDataColumn.DataType,
                            Nullable = myDataColumn.AllowDBNull
                        });
                    }

                }
                finally
                {
                    sqlConnection.Close();
                }

                return result;
            });
        }


        private static Type GetColumnType(string typeName)
        {
            typeName = typeName.ToLower();

            if (typeName == "image")
                return typeof(byte[]);
            if (typeName == "text")
                return typeof(string);
            if (typeName == "tinyint")
                return typeof(short);
            if (typeName == "smallint")
                return typeof(short);
            if (typeName == "int")
                return typeof(int);
            if (typeName == "smalldatetime")
                return typeof(DateTime);
            if (typeName == "real")
                return typeof(double);
            if (typeName == "money")
                return typeof(double);
            if (typeName == "datetime")
                return typeof(DateTime);
            if (typeName == "float")
                return typeof(double);
            if (typeName == "ntext")
                return typeof(string);
            if (typeName == "bit")
                return typeof(bool);
            if (typeName == "decimal")
                return typeof(double);
            if (typeName == "numeric")
                return typeof(double);
            if (typeName == "smallmoney")
                return typeof(double);
            if (typeName == "bigint")
                return typeof(long);
            if (typeName == "varbinary")
                return typeof(byte[]);
            if (typeName == "varchar")
                return typeof(string);
            if (typeName == "binary")
                return typeof(byte[]);
            if (typeName == "char")
                return typeof(char);
            if (typeName == "timestamp")
                return typeof(DateTime);
            if (typeName == "nvarchar")
                return typeof(string);
            if (typeName == "nchar")
                return typeof(string);
            if (typeName == "uniqueidentifier")
                return typeof(Guid);
            if (typeName == "xml")
                return typeof(string);

            throw new Exception("Unknown type");
        }

        private static T PerformSQLActions<T>(string serverIp, bool useIntegrated, string username, string password, string databaseName, Func<SqlConnection, T> doStuff)
        {
            string connectionString = "";

            if (useIntegrated)
                connectionString = "Data Source=" + serverIp + @";Integrated Security=True;";
            else
                connectionString = @"Data Source=" + serverIp + @";User ID=" + username + ";Password=" + password + ";";

            if (!string.IsNullOrEmpty(databaseName))
                connectionString += ";Initial Catalog=" + databaseName;

            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();

                    return doStuff(sqlConnection);
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }
    }
}
